package com.itheima.health.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.itheima.health.pojo.Member;
import com.itheima.health.vo.PoiVO;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

public interface MemberMapper extends BaseMapper<Member> {
    @Select("select count(tm.id) memberCounts,CONCAT(rp.fyear,'-',rp.fmonth) yearAndMonth " +

            "from rpt_date rp, t_member tm " +

            "where rp.fdate = tm.REGTIME and year(now())=rp.fyear " +

            "group by rp.fmonth")
    ArrayList<PoiVO> findmember();

    @Select("select count(tm.id) memberCounts,CONCAT(rp.fyear,'-',rp.fmonth) yearAndMonth " +

            "from rpt_date rp, t_member tm " +

            "where rp.fdate = tm.REGTIME and rp.fdate BETWEEN #{begin} and #{end} " +

            "group by rp.fmonth")
    ArrayList<PoiVO> findMemberByDate(@Param("begin") String begin, @Param("end") String end);

    @Select("SELECT COUNT(sex) value,SEX name " +

            "FROM t_member " +

            "GROUP BY sex ")
    List<HashMap> findmemberBySex();

    @Select("SELECT (CASE " +
            "WHEN a.age BETWEEN 0 AND 18 THEN '0-18岁' " +
            "WHEN a.age BETWEEN 19 AND 30 THEN '19-30岁' " +
            "WHEN a.age BETWEEN 31 AND 45 THEN '31-45岁' " +
            "WHEN a.age BETWEEN 46 AND 60 THEN '46-60岁' " +
            "WHEN a.age BETWEEN 61 AND 75 THEN '61-75岁' " +
            "WHEN a.age >75 THEN '75岁以上'END) AS name, COUNT(*) 'value' FROM " +
            "(SELECT(YEAR (curdate())- YEAR ( birthday )- 1+ (DATE_FORMAT( birthday, '%m%d' )<=(DATE_FORMAT( curdate(), '%m%d' )))) age FROM t_member ) a GROUP BY name")
    List<HashMap> findmemberByAge();
}
